//
//  Database.swift
//  position
//
//  Created by HzS on 2023/1/9.
//

import Foundation
import PoSQLite
import CoreLocation

final class Database {
    
    /// 数据库
    private var database: SQLiteMultipartDatabase?
    
    init() {
        var uuid: String? = UserDefaults.standard.string(forKey: "uuid")
        if uuid == nil {
            uuid = UUID().uuidString
            UserDefaults.standard.set(uuid, forKey: "uuid")
        }
        update(with: uuid!)
    }
    
    func update(with uuid: String) {
        guard var path = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first else { return }
        path = (path as NSString).appendingPathComponent("database")
        if !FileManager.default.fileExists(atPath: path) {
            try? FileManager.default.createDirectory(atPath: path, withIntermediateDirectories: true)
        }
        path = (path as NSString).appendingPathComponent("\(uuid).db")
        database = SQLiteMultipartDatabase(path: path)
        database?.doWithTransaction { db in
            do {
                try db.execute(sql: "CREATE TABLE IF NOT EXISTS location_list (timestamp INTEGER DEFAULT 0, longitude REAL DEFAULT 0, latitude REAL DEFAULT 0, horizon_accuracy REAL DEFAULT 0);", isWrite: false)
                try db.execute(sql: "CREATE INDEX IF NOT EXISTS location_list_timestamp_index ON location_list (timestamp);", isWrite: false)
            } catch {
                debugPrint("创建表格失败: \(error.localizedDescription)")
            }
        }
    }
    
    func save(location: CLLocation, horizontalAccuracy: CLLocationDistance) {
        try? database?.executeUpdate(statement: "INSERT OR REPLACE INTO location_list (timestamp, longitude, latitude, horizon_accuracy) VALUES (?, ?, ?, ?);") { stmt in
            try stmt.bind(position: 1, Int(location.timestamp.timeIntervalSince1970 * 1000))
            try stmt.bind(position: 2, location.coordinate.longitude)
            try stmt.bind(position: 3, location.coordinate.latitude)
            try stmt.bind(position: 4, horizontalAccuracy)
        }
    }
    
    func getHistoryLocations(startTimestamp: Int, endTimestamp: Int) -> [CLLocation] {
        let statement = "SELECT timestamp, longitude, latitude, horizon_accuracy FROM location_list WHERE timestamp > ? AND timestamp < ? ORDER BY timestamp ASC"
        var locations = [CLLocation]()
        try? database?.executeQuery(statement: statement, doBindings: { stmt in
            try stmt.bind(position: 1, startTimestamp)
            try stmt.bind(position: 2, endTimestamp)
        }, handleRow: { stmt, stop in
            let time = stmt.columnInt(position: 0)
            let timeInterval = Double(time) / 1000.0
            let timestamp = Date(timeIntervalSince1970: timeInterval)
            let longitude = stmt.columnDouble(position: 1)
            let latitude = stmt.columnDouble(position: 2)
            let speed = 0.0
            let horizonAccuracy = stmt.columnDouble(position: 3)
            let location = CLLocation(coordinate: CLLocationCoordinate2D(latitude: latitude, longitude: longitude), altitude: 0, horizontalAccuracy: horizonAccuracy, verticalAccuracy: 0, course: -1, speed: speed, timestamp: timestamp)
            locations.append(location)
        })
        return locations
    }
    
    func getEarliestLocation() -> CLLocation? {
        var location: CLLocation?
        try? database?.executeQuery(statement: "SELECT timestamp, longitude, latitude, horizon_accuracy FROM location_list ORDER BY timestamp ASC LIMIT 1", doBindings: { stmt in
            
        }, handleRow: { stmt, stop in
            let time = stmt.columnInt(position: 0)
            let timeInterval = Double(time) / 1000.0
            let timestamp = Date(timeIntervalSince1970: timeInterval)
            let longitude = stmt.columnDouble(position: 1)
            let latitude = stmt.columnDouble(position: 2)
            let speed = 0.0
            let horizonAccuracy = stmt.columnDouble(position: 3)
            location = CLLocation(coordinate: CLLocationCoordinate2D(latitude: latitude, longitude: longitude), altitude: 0, horizontalAccuracy: horizonAccuracy, verticalAccuracy: 0, course: -1, speed: speed, timestamp: timestamp)
        })
        return location
    }
    
    func getLastLocation() -> CLLocation? {
        var location: CLLocation?
        try? database?.executeQuery(statement: "SELECT timestamp, longitude, latitude, horizon_accuracy FROM location_list ORDER BY timestamp DESC LIMIT 1", doBindings: { stmt in
            
        }, handleRow: { stmt, stop in
            let time = stmt.columnInt(position: 0)
            let timeInterval = Double(time) / 1000.0
            let timestamp = Date(timeIntervalSince1970: timeInterval)
            let longitude = stmt.columnDouble(position: 1)
            let latitude = stmt.columnDouble(position: 2)
            let speed = 0.0
            let horizonAccuracy = stmt.columnDouble(position: 3)
            location = CLLocation(coordinate: CLLocationCoordinate2D(latitude: latitude, longitude: longitude), altitude: 0, horizontalAccuracy: horizonAccuracy, verticalAccuracy: 0, course: -1, speed: speed, timestamp: timestamp)
        })
        return location
    }
    
    func removeLocations(_ locations: [CLLocation]) {
        database?.doWithTransaction { db in
            for location in locations {
                try db.execute(sql: "DELET FROM location_list WHERE timestamp=\(Int(location.timestamp.timeIntervalSince1970 * 1000))", isWrite: true)
            }
        }
    }
}
